SQL高级

创建学生表

--删除学生表
drop table student;

 --创建学生表
create table student(
      stu_id number(4) constraint pk_student_stu_id primary key,--设置主键
      stu_name char(10) constraint unique_stu_name unique,--设置唯一约束，名字不能为空
      stu_email varchar2(50) constraint chk_stu_email check(stu_email like '%@%')--检查约束，邮箱必须要有@符号
);

创建学分表

--删除表
drop table score;

--创建学分表
create table score(
      sc_id number(3),
      sc_stu_id number(4) not null,
      sc_course_id number(3) not null,
      sc_score number(4,1),
      constraint pk_sc_id primary key(sc_id),--设置主键
      constraint fk_sc_stu_id foreign key(sc_stu_id)
      references student(stu_id)   --设置外键
);

插入学生数据

--插入学生信息

insert into student
values(1,'张三','zhangsan@.com');

insert into student
values(2,'李四','lisi@.com');

insert into student
values(3,'王五','wamgwu@.com');

insert into student
values(4,'赵六','zhaoliu@.com');

insert into student
values(5,'田七','tianqi@.com');

更新id为3的学生信息

--更新表信息

update student set stu_name='wangwu' where stu_id=3;

插入学生分数信息

--插入分数信息

insert into score values(1,1,1,90);

insert into score values(4,1,2,85);

insert into score values(3,2,1,75);

insert into score values(2,1,2,85);

修改表字段

--修改表字段

alter table student modify stu_email varchar2(100);

--添加字段

alter table student add stu_address varchar2(200);

--删除字段

alter table student drop column stu_address;

--修改表名

alter table students rename to student;

select * from student;

--删除表

drop table students;--执行不成功，表的主键作为score表的外键

内连接查询

--内连接查询
select *
from student s
inner join score sc
on s.stu_id = sc.sc_stu_id where sc.sc_score>60;

--一般的连接查询
select * from student s,score sc where s.stu_id=sc.sc_stu_id;

左外连接

select * from score sc left join student s
on s.stu_id=sc.sc_stu_id;

右外连接

--右外连接

select * from score sc right join student s
on s.stu_id=sc.sc_stu_id;

select * from student s right join  score sc
on s.stu_id=sc.sc_stu_id;

全外连接

--全外连接
select * from student s full join  score sc
on s.stu_id=sc.sc_stu_id;

子查询

--子查询

select * from
student s,(select * from score) sc
where s.stu_id=sc.sc_stu_id;

--用in内部查询结果少，外部结果多
select * from
student s where 
s.stu_id  not in(select sc.sc_id from score sc where sc.sc_id=s.stu_id);

--用exists内部部结果多，外部结果少

select * from
student s where not
exists(select sc.sc_id from score sc where sc.sc_id=s.stu_id);